Grokking SQL for Tech Interviews
Rate this course
Ask Author
Contribute
Back to course home

1% completed

Vote For New Content
Introduction
Course Overview
What is RDBMS?
What is SQL?
A Quick Review of SQL
Read data from table
WHERE Clause
Operators
Handle NULLs in SQL
DISTINCT Clause
ORDER BY Clause
LIMIT and OFFSET
GROUP BY
HAVING
Aggregate Functions
Order of Execution
Order of SQL Query Execution
SQL Functions
Number Functions
String Functions
Date Functions
Nested Queries
Nested Query
Types of Subqueries
JOINS
Introduction
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
SELF JOIN
SET Operations
What are SET operations?
UNION
INTERSECT
EXCEPT
A Quick Review of DDL
Keys in SQL
CREATE
ALTER
DROP
TRUNCATE
Indexes
A Quick Review of DML
INSERT
UPDATE
DELETE
Database Backup & Restore
Backup Database
Restore Database
Interview Problems - Easy
1. Major Cities
2. Not boring movies
3. Triangle Judgement
4. Biggest Single Number
5. Product Sales Analysis I
6. Product Sales Analysis II
7. Game Play Analysis I
8. Game Play Analysis II
9. Queries Quality and Percentage
10. Average Selling Price
11. Weather Type in Each Country
12. Students and Examinations
13. Ads Performance
14. Group Sold Products By The Date
15. Employee Email Verification
16. Sellers With No Sales
17. Average Time of Process
18. Find Followers Count
19. Ad-Free Sessions
20. Bikes Last Time Used
21. Daily User Engagement Levels
22. Employee Attendance Record
23. Library Book Loans
24. Customer Purchase Summary
25. Long Comments (Easy)
26. Unused Accounts
27. Patient Appointment No-Shows
28. Books and Authors
29. High School Attendance
30. Employee Absences
31. Green Product Identification (Easy)
32. Employee Department Exclusion (Easy)
33. Sales Person (Easy)
34. Employee Task Tracking (Easy)
Interview Problems - Medium
1. Winning Candidate
2. Second Degree Follower
3. 2nd Highest Salary
4. Game Play Analysis III
5. Game Play Analysis IV
6. Active Businesses
7. Product Price on a Specific Date
8. Monthly Transactions II
9. Team Scores in Football Tournament
10. Page Recommendations
11. Running Total for Different Genders
12. Evaluate Boolean Expression
13. Active Users
14. Find Interview Candidates
15. Suspicious Bank Accounts
16. Order Fulfillment Rate (Medium)
17. Last Person to Fit in the Elevator (Medium)
18. Retail Expansion Analysis (Medium)
19. Employee Collaboration Networks (Medium)
20. Second Fastest Time in Races (Medium)
Interview Problems - Hard
1. Trips and Users
2. Human Traffic of Stadium
3. Students Report By Geography
4. Game Play Analysis V
5. Number of Transactions per Visit
6. Find the Quiet Students in All Exams
7. First and Last Call On the Same Day
8. Build the Equation
9. Longest Winning Streak
10. Dynamic Pivoting of a Table
11. Dynamic Unpivoting of a Table
12. Generate the Invoice
13. Popularity Percentage
14. Viewers Turned Streamers
15. Status of Flight Tickets
1. High Performing Employees (Easy)
2. Student Course Averages (Easy)
3. Library Book Checkout Percentage (Easy)
4. Employee Performance and Improvement Rate (Easy)
5. Find Popular Posts (Easy)
6. Main Office Assignment for Each Employee (Easy)
7. Rectangle Validation (Easy)
8. Library Book Checkouts by Date (Easy)
9. Same-Day Appointment Fulfillment (Medium)
10. Departments with High Earning Employees (Medium)
11. Students Who Attended All Courses (Medium)
12. Library Book Checkouts First Year (Medium)
13. Repeated Values in Sequence (Medium)
14. School Top Achievers by Subject (Hard)
10. Dynamic Pivoting of a Table

Problem

Table: Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store       | varchar |
| price       | int     |
+-------------+---------+
(product_id, store) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates the price of product_id in store.
There will be at most 30 different stores in the table.
price is the price of the product at this store.

Problem Definition

Implement the procedure PivotProducts to reorganize the Products table so that each row has the id of one product and its price in each store. The price should be null if the product is not sold in a store. The columns of the table should contain each store and they should be sorted in lexicographical order.

The procedure should return the table after reorganizing it.

Return the result table in any order.

Example

Image

Output

Image

Solution

To transform the Products table into a pivoted format where each row represents a unique product_id with its corresponding prices across various stores, we need to dynamically generate SQL statements. This is essential because the number of stores can vary, and SQL doesn't inherently support dynamic column generation in static queries. The provided stored procedure PivotProducts accomplishes this using dynamic SQL, window functions, and conditional aggregation. Below is a comprehensive breakdown of the approach, step-by-step execution, and detailed explanations for each component of the SQL query.

Approach Overview

  1. Understand the Data Structure:

    • Each row in the Products table represents the price of a specific product in a particular store.
    • The combination of (product_id, store) is unique, ensuring that each product-store pair appears only once.
  2. Identify Unique Stores:

    • Since the number of stores can vary (up to 30 as per the problem statement), we need a dynamic way to handle each store as a separate column in the pivoted result.
  3. Construct Dynamic SQL for Pivoting:

    • Use GROUP_CONCAT to dynamically generate the SQL segments required for each store.
    • Aggregate the prices using conditional logic to place them under their respective store columns.
  4. Execute the Dynamic SQL:

    • Prepare, execute, and deallocate the dynamically constructed SQL statement to produce the final pivoted table.

SQL Query

CREATE PROCEDURE PivotProducts() BEGIN # Write your MySQL query statement below. SET group_concat_max_len = 1000000; #This is tricky. There's a length limit on GROUP_CONCAT. SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(IF(store = "', store, '", price, null)) AS ', store) ORDER BY store ASC) INTO @sql FROM Products; SET @sql = CONCAT('SELECT product_id, ', @sql, ' FROM Products GROUP BY product_id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END

Step-by-Step Explanation

Step 1: Increase GROUP_CONCAT Maximum Length

SET group_concat_max_len = 1000000; #This is tricky. There's a length limit on GROUP_CONCAT.
  • The GROUP_CONCAT function in MySQL has a default maximum length for the concatenated string it can produce. By setting group_concat_max_len to a large value (1,000,000 in this case), we ensure that the dynamic SQL string generated will not be truncated, especially when dealing with numerous stores.

Step 2: Initialize the Dynamic SQL Variable

SET @sql = NULL;
  • Initializes the user-defined variable @sql to NULL. This variable will later store the dynamically constructed SQL statement.

Step 3: Dynamically Generate the SELECT Clause for Each Store

SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(IF(store = "', store, '", price, null)) AS ', store) ORDER BY store ASC) INTO @sql FROM Products;
  • Purpose:

    • GROUP_CONCAT: Aggregates the generated SQL segments for each store into a single comma-separated string.
    • DISTINCT: Ensures that each store is processed only once, eliminating duplicates.
    • CONCAT: Constructs the SQL expression for each store.
  • Breakdown of the CONCAT Function:

    • 'SUM(IF(store = "', store, '", price, null)) AS ', store:
      • IF(store = "StoreName", price, null): Checks if the current row's store matches the store being processed. If it does, it returns the price; otherwise, it returns null.
      • SUM(IF(...)): Aggregates the prices for each store. Since each (product_id, store) pair is unique, the SUM effectively retrieves the price if the store matches, or null otherwise.
      • AS StoreName: Aliases the resulting sum as the store's name, creating a column with the store's name in the final result.
  • ORDER BY store ASC:
    Ensures that the store columns are ordered lexicographically (alphabetically) as required.

  • Result of GROUP_CONCAT:
    Generates a string like:

    SUM(IF(store = "LC_Store", price, null)) AS LC_Store,
    SUM(IF(store = "Nozama", price, null)) AS Nozama,
    SUM(IF(store = "Shop", price, null)) AS Shop,
    SUM(IF(store = "Souq", price, null)) AS Souq
    
  • INTO @sql:
    Stores the concatenated string into the @sql variable for later use.

Step 4: Construct the Final Dynamic SQL Statement

SET @sql = CONCAT('SELECT product_id, ', @sql, ' FROM Products GROUP BY product_id');
  • Purpose:

    • CONCAT: Combines static and dynamic parts of the SQL query.
    • 'SELECT product_id, ': Begins the SELECT statement by selecting product_id.
    • @sql: Inserts the dynamically generated SUM statements for each store.
    • ' FROM Products GROUP BY product_id': Specifies the data source and groups the results by product_id.
  • Resulting Dynamic SQL Statement (@sql):

    SELECT product_id, 
      SUM(IF(store = "LC_Store", price, null)) AS LC_Store,
      SUM(IF(store = "Nozama", price, null)) AS Nozama,
      SUM(IF(store = "Shop", price, null)) AS Shop,
      SUM(IF(store = "Souq", price, null)) AS Souq
    FROM Products
    GROUP BY product_id
    
  • This dynamic SQL statement effectively pivots the Products table, turning unique store names into individual columns with their corresponding prices. If a product isn't sold in a particular store, the SUM(IF(...)) expression will return null for that store's column.

Step 5: Prepare and Execute the Dynamic SQL Statement

PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  • PREPARE stmt FROM @sql;

    • Prepares the dynamically constructed SQL statement stored in @sql for execution.
  • EXECUTE stmt;

    • Executes the prepared statement stmt.
  • DEALLOCATE PREPARE stmt;

    • Frees the resources associated with the prepared statement stmt.

Final Pivoted Output

Executing the stored procedure PivotProducts() will produce a result set similar to the following, depending on the data in the Products table:

+------------+-----------+-------+-------+-------+ | product_id | LC_Store | Nozama| Shop | Souq | +------------+-----------+-------+-------+-------+ | 1 | 100 | NULL | 110 | NULL | | 2 | NULL | 200 | NULL | 190 | | 3 | NULL | NULL | 1000 | 1900 | +------------+-----------+-------+-------+-------+
  • Interpretation:

    • product_id = 1:
      • LC_Store: $100
      • Shop: $110
      • Nozama & Souq: Not available (NULL)
    • product_id = 2:
      • Nozama: $200
      • Souq: $190
      • LC_Store & Shop: Not available (NULL)
    • product_id = 3:
      • Shop: $1000
      • Souq: $1900
      • LC_Store & Nozama: Not available (NULL)
    • product_id = 4:
      • Shop: $200
      • Souq: $300
      • LC_Store & Nozama: Not available (NULL)
  • Note:
    If a product is not sold in a particular store, the corresponding column will display NULL as required.

9. Longest Winning Streak
11. Dynamic Unpivoting of a Table
Mark as Completed

On this page

Problem

Problem Definition

Example

Output

Solution

Approach Overview

SQL Query

Step-by-Step Explanation